######### Notes ###########

# Demographics: All
#               Male, Female
#               16-24, 25-49, 50-64, 65+

# Other demographics not included
## Race (but non white categories very small n...)
## Class


######### BES Internet Panel ###########

library(tidyverse)
library(haven)

setwd("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Originals/BES") 

#read_sav("2014-2023 BES IP.sav") -> data

#data %>%
#  select(id, starts_with('ageW'), gender, starts_with('p_ethnicityW'),
#         starts_with('wave'), starts_with('wt_new_'), starts_with('wt_full_'),
#         starts_with('mii_cat'), starts_with('selfprior'),
#         starts_with('changeCrimeW'), starts_with('al2'), starts_with('al5')) -> subset

#write_csv(subset, "/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Finished/BESIP_allrepeatedcrimequestions.csv")

subset <- read_csv("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Finished/BESIP_allrepeatedcrimequestions.csv")

subset %>%
  mutate(gender = case_when(gender == 1 ~ 'Male', gender == 2 ~ "Female")) %>%
  mutate(across(starts_with('mii_cat'),
                ~if_else(. == 14, 1, 
                         ifelse(is.na(.), NA_integer_, 0)), .names = "crime_{col}")) %>% # 1 if mii is crime, 0 otherwise
  mutate(across(starts_with('mii_cat'),
                ~if_else(. == 1, 1, 
                         ifelse(is.na(.), NA_integer_, 0)), .names = "health_{col}")) %>% # 1 if mii is crime, 0 otherwise
  mutate(across(starts_with('mii_cat'),
                ~if_else(. == 2, 1, 
                         ifelse(is.na(.), NA_integer_, 0)), .names = "edu_{col}")) %>% # 1 if mii is crime, 0 otherwise
  mutate(across(starts_with('mii_cat'),
                ~if_else(. %in% c(21:24), 1, 
                         ifelse(is.na(.), NA_integer_, 0)), .names = "wardef_{col}")) %>% # 1 if mii is crime, 0 otherwise
  mutate(across(c(selfPriorities_crimeW17, selfPriorities_crimeW20),
                ~if_else(. == 9999, 0, .))) %>% # 1 if is a priority, 0 otherwise
  mutate(across(starts_with('changeCrimeW'),
                ~case_match(., c(4,5)~1, c(1,2,9999)~0))) %>% # 1 if getting a little / a lot higher, 0 otherwise
  mutate(across(starts_with('al2'),
                ~case_match(., c(4,5)~1, c(1,2,9999)~0))) %>% # 1 if agree or agree strongly with death pen, 0 otherwise
  mutate(across(starts_with('al5'),
                ~case_match(., c(4,5)~1, c(1,2,9999)~0))) %>% # 1 if agree or agree strongly with stiffer sentences, 0 otherwise
  mutate(across(starts_with('ageW'),
                ~case_when(. > 15 & . < 25 ~ "16-24",    
                           . > 24 & . < 50 ~ "25-49",    
                           . > 49 & . < 65 ~ "50-64",    
                           . > 64 ~ "65+"))) %>%
  mutate(across(starts_with('p_ethnicityW'),
                ~case_when(. %in% c(1,2) ~ "White",
                           . %in% c(3,4,5,6,7,8,9,10,11,12,13,14,15) ~ 'Non-white'))) -> subset

subset %>%
  filter(wave1 == 1) %>%
  drop_na(wt_full_W1) %>%
  summarise(across(c(crime_mii_catW1, health_mii_catW1, edu_mii_catW1, wardef_mii_catW1, changeCrimeW1, al2W1_W5, al5W1_W5),
                   \(x) weighted.mean(x, wt_full_W1, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave1 == 1) %>%
              drop_na(wt_full_W1, ageW1) %>%
              group_by(ageW1) %>%
              summarise(across(c(crime_mii_catW1, health_mii_catW1, edu_mii_catW1, wardef_mii_catW1, changeCrimeW1, al2W1_W5, al5W1_W5),
                               \(x) weighted.mean(x, wt_full_W1, na.rm=T))) %>%
              rename(Demographic = ageW1)) %>%
  bind_rows(subset %>%
              filter(wave1 == 1) %>%
              drop_na(wt_full_W1, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW1, health_mii_catW1, edu_mii_catW1, wardef_mii_catW1, changeCrimeW1, al2W1_W5, al5W1_W5),
                               \(x) weighted.mean(x, wt_full_W1, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave1 == 1) %>%
              drop_na(wt_full_W1, p_ethnicityW1) %>%
              group_by(p_ethnicityW1) %>%
              summarise(across(c(crime_mii_catW1, health_mii_catW1, edu_mii_catW1, wardef_mii_catW1, changeCrimeW1, al2W1_W5, al5W1_W5),
                               \(x) weighted.mean(x, wt_full_W1, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW1)) %>%
  mutate(Wave = 1) %>%
  rename(mii_crime = crime_mii_catW1, mii_health = health_mii_catW1, mii_edu = edu_mii_catW1, mii_defence = wardef_mii_catW1,
         increasecrime = changeCrimeW1, deathpen = al2W1_W5, stiffsent = al5W1_W5) -> OutPut


subset %>%
  filter(wave2 == 1) %>%
  drop_na(wt_full_W2) %>%
  summarise(across(c(crime_mii_catW2, health_mii_catW2, edu_mii_catW2, wardef_mii_catW2, changeCrimeW2),
                   \(x) weighted.mean(x, wt_full_W2, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave2 == 1) %>%
              drop_na(wt_full_W2, ageW2) %>%
              group_by(ageW2) %>%
              summarise(across(c(crime_mii_catW2, health_mii_catW2, edu_mii_catW2, wardef_mii_catW2, changeCrimeW2),
                               \(x) weighted.mean(x, wt_full_W2, na.rm=T))) %>%
              rename(Demographic = ageW2)) %>%
  bind_rows(subset %>%
              filter(wave2 == 1) %>%
              drop_na(wt_full_W2, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW2, health_mii_catW2, edu_mii_catW2, wardef_mii_catW2, changeCrimeW2),
                               \(x) weighted.mean(x, wt_full_W2, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave2 == 1) %>%
              drop_na(wt_full_W2, p_ethnicityW2) %>%
              group_by(p_ethnicityW2) %>%
              summarise(across(c(crime_mii_catW2, health_mii_catW2, edu_mii_catW2, wardef_mii_catW2, changeCrimeW2),
                               \(x) weighted.mean(x, wt_full_W2, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW2)) %>%
  mutate(Wave = 2) %>%
  rename(mii_crime = crime_mii_catW2, mii_health = health_mii_catW2, mii_edu = edu_mii_catW2, mii_defence = wardef_mii_catW2,
         increasecrime = changeCrimeW2) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave3 == 1) %>%
  drop_na(wt_full_W3) %>%
  summarise(across(c(crime_mii_catW3, health_mii_catW3, edu_mii_catW3, wardef_mii_catW3, changeCrimeW3),
                   \(x) weighted.mean(x, wt_full_W3, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave3 == 1) %>%
              drop_na(wt_full_W3, ageW3) %>%
              group_by(ageW3) %>%
              summarise(across(c(crime_mii_catW3, health_mii_catW3, edu_mii_catW3, wardef_mii_catW3, changeCrimeW3),
                               \(x) weighted.mean(x, wt_full_W3, na.rm=T))) %>%
              rename(Demographic = ageW3)) %>%
  bind_rows(subset %>%
              filter(wave3 == 1) %>%
              drop_na(wt_full_W3, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW3, health_mii_catW3, edu_mii_catW3, wardef_mii_catW3, changeCrimeW3),
                               \(x) weighted.mean(x, wt_full_W3, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave3 == 1) %>%
              drop_na(wt_full_W3, p_ethnicityW3) %>%
              group_by(p_ethnicityW3) %>%
              summarise(across(c(crime_mii_catW3, health_mii_catW3, edu_mii_catW3, wardef_mii_catW3, changeCrimeW3),
                               \(x) weighted.mean(x, wt_full_W3, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW3)) %>%
  mutate(Wave = 3) %>%
  rename(mii_crime = crime_mii_catW3, mii_health = health_mii_catW3, mii_edu = edu_mii_catW3, mii_defence = wardef_mii_catW3,
         increasecrime = changeCrimeW3) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave4 == 1) %>%
  drop_na(wt_full_W4) %>%
  summarise(across(c(crime_mii_catW4, health_mii_catW4, edu_mii_catW4, wardef_mii_catW4, changeCrimeW4),
                   \(x) weighted.mean(x, wt_full_W4, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave4 == 1) %>%
              drop_na(wt_full_W4, ageW4) %>%
              group_by(ageW4) %>%
              summarise(across(c(crime_mii_catW4, health_mii_catW4, edu_mii_catW4, wardef_mii_catW4, changeCrimeW4),
                               \(x) weighted.mean(x, wt_full_W4, na.rm=T))) %>%
              rename(Demographic = ageW4)) %>%
  bind_rows(subset %>%
              filter(wave4 == 1) %>%
              drop_na(wt_full_W4, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW4, health_mii_catW4, edu_mii_catW4, wardef_mii_catW4, changeCrimeW4),
                               \(x) weighted.mean(x, wt_full_W4, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave4 == 1) %>%
              drop_na(wt_full_W4, p_ethnicityW4) %>%
              group_by(p_ethnicityW4) %>%
              summarise(across(c(crime_mii_catW4, health_mii_catW4, edu_mii_catW4, wardef_mii_catW4, changeCrimeW4),
                               \(x) weighted.mean(x, wt_full_W4, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW4)) %>%
  mutate(Wave = 4) %>%
  rename(mii_crime = crime_mii_catW4, mii_health = health_mii_catW4, mii_edu = edu_mii_catW4, mii_defence = wardef_mii_catW4,
         increasecrime = changeCrimeW4) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave5 == 1) %>%
  drop_na(wt_full_W5) %>%
  summarise(across(c(crime_mii_catW5, health_mii_catW5, edu_mii_catW5, wardef_mii_catW5),
                   \(x) weighted.mean(x, wt_full_W5, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave5 == 1) %>%
              drop_na(wt_full_W5, ageW5) %>%
              group_by(ageW5) %>%
              summarise(across(c(crime_mii_catW5, health_mii_catW5, edu_mii_catW5, wardef_mii_catW5),
                               \(x) weighted.mean(x, wt_full_W5, na.rm=T))) %>%
              rename(Demographic = ageW5)) %>%
  bind_rows(subset %>%
              filter(wave5 == 1) %>%
              drop_na(wt_full_W5, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW5, health_mii_catW5, edu_mii_catW5, wardef_mii_catW5),
                               \(x) weighted.mean(x, wt_full_W5, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave5 == 1) %>%
              drop_na(wt_full_W5, p_ethnicityW5) %>%
              group_by(p_ethnicityW5) %>%
              summarise(across(c(crime_mii_catW5, health_mii_catW5, edu_mii_catW5, wardef_mii_catW5),
                               \(x) weighted.mean(x, wt_full_W5, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW5)) %>%
  mutate(Wave = 5) %>%
  rename(mii_crime = crime_mii_catW5, mii_health = health_mii_catW5, mii_edu = edu_mii_catW5, mii_defence = wardef_mii_catW5) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave6 == 1) %>%
  drop_na(wt_new_W6) %>%
  summarise(across(c(crime_mii_catW6, health_mii_catW6, edu_mii_catW6, wardef_mii_catW6, al2W6, al5W6),
                   \(x) weighted.mean(x, wt_new_W6, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave6 == 1) %>%
              drop_na(wt_new_W6, ageW6) %>%
              group_by(ageW6) %>%
              summarise(across(c(crime_mii_catW6, health_mii_catW6, edu_mii_catW6, wardef_mii_catW6, al2W6, al5W6),
                               \(x) weighted.mean(x, wt_new_W6, na.rm=T))) %>%
              rename(Demographic = ageW6)) %>%
  bind_rows(subset %>%
              filter(wave6 == 1) %>%
              drop_na(wt_new_W6, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW6, health_mii_catW6, edu_mii_catW6, wardef_mii_catW6, al2W6, al5W6),
                               \(x) weighted.mean(x, wt_new_W6, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave6 == 1) %>%
              drop_na(wt_new_W6, p_ethnicityW6) %>%
              group_by(p_ethnicityW6) %>%
              summarise(across(c(crime_mii_catW6, health_mii_catW6, edu_mii_catW6, wardef_mii_catW6, al2W6, al5W6),
                               \(x) weighted.mean(x, wt_new_W6, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW6)) %>%
  mutate(Wave = 6) %>%
  rename(mii_crime = crime_mii_catW6, mii_health = health_mii_catW6, mii_edu = edu_mii_catW6, mii_defence = wardef_mii_catW6,
         deathpen = al2W6, stiffsent = al5W6) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave7 == 1) %>%
  drop_na(wt_new_W7) %>%
  summarise(across(c(crime_mii_catW7, health_mii_catW7, edu_mii_catW7, wardef_mii_catW7, changeCrimeW7, al2W7_W9, al5W7_W9),
                   \(x) weighted.mean(x, wt_new_W7, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave7 == 1) %>%
              drop_na(wt_new_W7, ageW7) %>%
              group_by(ageW7) %>%
              summarise(across(c(crime_mii_catW7, health_mii_catW7, edu_mii_catW7, wardef_mii_catW7, changeCrimeW7, al2W7_W9, al5W7_W9),
                               \(x) weighted.mean(x, wt_new_W7, na.rm=T))) %>%
              rename(Demographic = ageW7)) %>%
  bind_rows(subset %>%
              filter(wave7 == 1) %>%
              drop_na(wt_new_W7, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW7, health_mii_catW7, edu_mii_catW7, wardef_mii_catW7, changeCrimeW7, al2W7_W9, al5W7_W9),
                               \(x) weighted.mean(x, wt_new_W7, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave7 == 1) %>%
              drop_na(wt_new_W7, p_ethnicityW7) %>%
              group_by(p_ethnicityW7) %>%
              summarise(across(c(crime_mii_catW7, health_mii_catW7, edu_mii_catW7, wardef_mii_catW7, changeCrimeW7, al2W7_W9, al5W7_W9),
                               \(x) weighted.mean(x, wt_new_W7, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW7)) %>%
  mutate(Wave = 7) %>%
  rename(mii_crime = crime_mii_catW7, mii_health = health_mii_catW7, mii_edu = edu_mii_catW7, mii_defence = wardef_mii_catW7,
         increasecrime = changeCrimeW7, deathpen = al2W7_W9, stiffsent = al5W7_W9) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave8 == 1) %>%
  drop_na(wt_new_W8) %>%
  summarise(across(c(crime_mii_catW8, health_mii_catW8, edu_mii_catW8, wardef_mii_catW8, changeCrimeW8),
                   \(x) weighted.mean(x, wt_new_W8, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave8 == 1) %>%
              drop_na(wt_new_W8, ageW8) %>%
              group_by(ageW8) %>%
              summarise(across(c(crime_mii_catW8, health_mii_catW8, edu_mii_catW8, wardef_mii_catW8, changeCrimeW8),
                               \(x) weighted.mean(x, wt_new_W8, na.rm=T))) %>%
              rename(Demographic = ageW8)) %>%
  bind_rows(subset %>%
              filter(wave8 == 1) %>%
              drop_na(wt_new_W8, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW8, health_mii_catW8, edu_mii_catW8, wardef_mii_catW8, changeCrimeW8),
                               \(x) weighted.mean(x, wt_new_W8, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave8 == 1) %>%
              drop_na(wt_new_W8, p_ethnicityW8) %>%
              group_by(p_ethnicityW8) %>%
              summarise(across(c(crime_mii_catW8, health_mii_catW8, edu_mii_catW8, wardef_mii_catW8, changeCrimeW8),
                               \(x) weighted.mean(x, wt_new_W8, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW8)) %>%
  mutate(Wave = 8) %>%
  rename(mii_crime = crime_mii_catW8, mii_health = health_mii_catW8, mii_edu = edu_mii_catW8, mii_defence = wardef_mii_catW8,
         increasecrime = changeCrimeW8) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave9 == 1) %>%
  drop_na(wt_new_W9) %>%
  summarise(across(c(crime_mii_catW9, health_mii_catW9, edu_mii_catW9, wardef_mii_catW9),
                   \(x) weighted.mean(x, wt_new_W9, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave9 == 1) %>%
              drop_na(wt_new_W9, ageW9) %>%
              group_by(ageW9) %>%
              summarise(across(c(crime_mii_catW9, health_mii_catW9, edu_mii_catW9, wardef_mii_catW9),
                               \(x) weighted.mean(x, wt_new_W9, na.rm=T))) %>%
              rename(Demographic = ageW9)) %>%
  bind_rows(subset %>%
              filter(wave9 == 1) %>%
              drop_na(wt_new_W9, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW9, health_mii_catW9, edu_mii_catW9, wardef_mii_catW9),
                               \(x) weighted.mean(x, wt_new_W9, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave9 == 1) %>%
              drop_na(wt_new_W9, p_ethnicityW9) %>%
              group_by(p_ethnicityW9) %>%
              summarise(across(c(crime_mii_catW9, health_mii_catW9, edu_mii_catW9, wardef_mii_catW9),
                               \(x) weighted.mean(x, wt_new_W9, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW9)) %>%
  mutate(Wave = 9) %>%
  rename(mii_crime = crime_mii_catW9, mii_health = health_mii_catW9, mii_edu = edu_mii_catW9, mii_defence = wardef_mii_catW9) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave10 == 1) %>%
  drop_na(wt_new_W10) %>%
  summarise(across(c(crime_mii_catW10, health_mii_catW10, edu_mii_catW10, wardef_mii_catW10, changeCrimeW10, al2W10_W12, al5W10_W12),
                   \(x) weighted.mean(x, wt_new_W10, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave10 == 1) %>%
              drop_na(wt_new_W10, ageW10) %>%
              group_by(ageW10) %>%
              summarise(across(c(crime_mii_catW10, health_mii_catW10, edu_mii_catW10, wardef_mii_catW10, changeCrimeW10, al2W10_W12, al5W10_W12),
                               \(x) weighted.mean(x, wt_new_W10, na.rm=T))) %>%
              rename(Demographic = ageW10)) %>%
  bind_rows(subset %>%
              filter(wave10 == 1) %>%
              drop_na(wt_new_W10, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW10, health_mii_catW10, edu_mii_catW10, wardef_mii_catW10, changeCrimeW10, al2W10_W12, al5W10_W12),
                               \(x) weighted.mean(x, wt_new_W10, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave10 == 1) %>%
              drop_na(wt_new_W10, p_ethnicityW10) %>%
              group_by(p_ethnicityW10) %>%
              summarise(across(c(crime_mii_catW10, health_mii_catW10, edu_mii_catW10, wardef_mii_catW10, changeCrimeW10, al2W10_W12, al5W10_W12),
                               \(x) weighted.mean(x, wt_new_W10, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW10)) %>%
  mutate(Wave = 10) %>%
  rename(mii_crime = crime_mii_catW10, mii_health = health_mii_catW10, mii_edu = edu_mii_catW10, mii_defence = wardef_mii_catW10,
         increasecrime = changeCrimeW10, deathpen = al2W10_W12, stiffsent = al5W10_W12) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave11 == 1) %>%
  drop_na(wt_new_W11) %>%
  summarise(across(c(crime_mii_catW11, health_mii_catW11, edu_mii_catW11, wardef_mii_catW11, changeCrimeW11),
                   \(x) weighted.mean(x, wt_new_W11, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave11 == 1) %>%
              drop_na(wt_new_W11, ageW11) %>%
              group_by(ageW11) %>%
              summarise(across(c(crime_mii_catW11, health_mii_catW11, edu_mii_catW11, wardef_mii_catW11, changeCrimeW11),
                               \(x) weighted.mean(x, wt_new_W11, na.rm=T))) %>%
              rename(Demographic = ageW11)) %>%
  bind_rows(subset %>%
              filter(wave11 == 1) %>%
              drop_na(wt_new_W11, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW11, health_mii_catW11, edu_mii_catW11, wardef_mii_catW11, changeCrimeW11),
                               \(x) weighted.mean(x, wt_new_W11, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave11 == 1) %>%
              drop_na(wt_new_W11, p_ethnicityW11) %>%
              group_by(p_ethnicityW11) %>%
              summarise(across(c(crime_mii_catW11, health_mii_catW11, edu_mii_catW11, wardef_mii_catW11, changeCrimeW11),
                               \(x) weighted.mean(x, wt_new_W11, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW11)) %>%
  mutate(Wave = 11) %>%
  rename(mii_crime = crime_mii_catW11, mii_health = health_mii_catW11, mii_edu = edu_mii_catW11, mii_defence = wardef_mii_catW11,
         increasecrime = changeCrimeW11) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave12 == 1) %>%
  drop_na(wt_new_W12) %>%
  summarise(across(c(crime_mii_catW12, health_mii_catW12, edu_mii_catW12, wardef_mii_catW12),
                   \(x) weighted.mean(x, wt_new_W12, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave12 == 1) %>%
              drop_na(wt_new_W12, ageW12) %>%
              group_by(ageW12) %>%
              summarise(across(c(crime_mii_catW12, health_mii_catW12, edu_mii_catW12, wardef_mii_catW12),
                               \(x) weighted.mean(x, wt_new_W12, na.rm=T))) %>%
              rename(Demographic = ageW12)) %>%
  bind_rows(subset %>%
              filter(wave12 == 1) %>%
              drop_na(wt_new_W12, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW12, health_mii_catW12, edu_mii_catW12, wardef_mii_catW12),
                               \(x) weighted.mean(x, wt_new_W12, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave12 == 1) %>%
              drop_na(wt_new_W12, p_ethnicityW12) %>%
              group_by(p_ethnicityW12) %>%
              summarise(across(c(crime_mii_catW12, health_mii_catW12, edu_mii_catW12, wardef_mii_catW12),
                               \(x) weighted.mean(x, wt_new_W12, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW12)) %>%
  mutate(Wave = 12) %>%
  rename(mii_crime = crime_mii_catW12, mii_health = health_mii_catW12, mii_edu = edu_mii_catW12, mii_defence = wardef_mii_catW12) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave13 == 1) %>%
  drop_na(wt_new_W13_result) %>%
  summarise(across(c(crime_mii_catW13, health_mii_catW13, edu_mii_catW13, wardef_mii_catW13, al2W13, al5W13),
                   \(x) weighted.mean(x, wt_new_W13_result, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave13 == 1) %>%
              drop_na(wt_new_W13_result, ageW13) %>%
              group_by(ageW13) %>%
              summarise(across(c(crime_mii_catW13, health_mii_catW13, edu_mii_catW13, wardef_mii_catW13, al2W13, al5W13),
                               \(x) weighted.mean(x, wt_new_W13_result, na.rm=T))) %>%
              rename(Demographic = ageW13)) %>%
  bind_rows(subset %>%
              filter(wave13 == 1) %>%
              drop_na(wt_new_W13_result, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW13, health_mii_catW13, edu_mii_catW13, wardef_mii_catW13, al2W13, al5W13),
                               \(x) weighted.mean(x, wt_new_W13_result, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave13 == 1) %>%
              drop_na(wt_new_W13_result, p_ethnicityW13) %>%
              group_by(p_ethnicityW13) %>%
              summarise(across(c(crime_mii_catW13, health_mii_catW13, edu_mii_catW13, wardef_mii_catW13, al2W13, al5W13),
                               \(x) weighted.mean(x, wt_new_W13_result, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW13)) %>%
  mutate(Wave = 13) %>%
  rename(mii_crime = crime_mii_catW13, mii_health = health_mii_catW13, mii_edu = edu_mii_catW13, mii_defence = wardef_mii_catW13,
         deathpen = al2W13, stiffsent = al5W13) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave14 == 1) %>%
  drop_na(wt_new_W14) %>%
  summarise(across(c(crime_mii_catW14, health_mii_catW14, edu_mii_catW14, wardef_mii_catW14, changeCrimeW14, al2W14W15, al5W14W15),
                   \(x) weighted.mean(x, wt_new_W14, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave14 == 1) %>%
              drop_na(wt_new_W14, ageW14) %>%
              group_by(ageW14) %>%
              summarise(across(c(crime_mii_catW14, health_mii_catW14, edu_mii_catW14, wardef_mii_catW14, changeCrimeW14, al2W14W15, al5W14W15),
                               \(x) weighted.mean(x, wt_new_W14, na.rm=T))) %>%
              rename(Demographic = ageW14)) %>%
  bind_rows(subset %>%
              filter(wave14 == 1) %>%
              drop_na(wt_new_W14, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW14, health_mii_catW14, edu_mii_catW14, wardef_mii_catW14, changeCrimeW14, al2W14W15, al5W14W15),
                               \(x) weighted.mean(x, wt_new_W14, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave14 == 1) %>%
              drop_na(wt_new_W14, p_ethnicityW14) %>%
              group_by(p_ethnicityW14) %>%
              summarise(across(c(crime_mii_catW14, health_mii_catW14, edu_mii_catW14, wardef_mii_catW14, changeCrimeW14, al2W14W15, al5W14W15),
                               \(x) weighted.mean(x, wt_new_W14, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW14)) %>%
  mutate(Wave = 14) %>%
  rename(mii_crime = crime_mii_catW14, mii_health = health_mii_catW14, mii_edu = edu_mii_catW14, mii_defence = wardef_mii_catW14,
         increasecrime = changeCrimeW14, deathpen = al2W14W15, stiffsent = al5W14W15) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave15 == 1) %>%
  drop_na(wt_new_W15) %>%
  summarise(across(c(crime_mii_catW15, health_mii_catW15, edu_mii_catW15, wardef_mii_catW15, changeCrimeW15),
                   \(x) weighted.mean(x, wt_new_W15, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave15 == 1) %>%
              drop_na(wt_new_W15, ageW15) %>%
              group_by(ageW15) %>%
              summarise(across(c(crime_mii_catW15, health_mii_catW15, edu_mii_catW15, wardef_mii_catW15, changeCrimeW15),
                               \(x) weighted.mean(x, wt_new_W15, na.rm=T))) %>%
              rename(Demographic = ageW15)) %>%
  bind_rows(subset %>%
              filter(wave15 == 1) %>%
              drop_na(wt_new_W15, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW15, health_mii_catW15, edu_mii_catW15, wardef_mii_catW15, changeCrimeW15),
                               \(x) weighted.mean(x, wt_new_W15, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave15 == 1) %>%
              drop_na(wt_new_W15, p_ethnicityW15) %>%
              group_by(p_ethnicityW15) %>%
              summarise(across(c(crime_mii_catW15, health_mii_catW15, edu_mii_catW15, wardef_mii_catW15, changeCrimeW15),
                               \(x) weighted.mean(x, wt_new_W15, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW15)) %>%
  mutate(Wave = 15) %>%
  rename(mii_crime = crime_mii_catW15, mii_health = health_mii_catW15, mii_edu = edu_mii_catW15, mii_defence = wardef_mii_catW15,
         increasecrime = changeCrimeW15) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave16 == 1) %>%
  drop_na(wt_new_W16) %>%
  summarise(across(c(crime_mii_catW16, health_mii_catW16, edu_mii_catW16, wardef_mii_catW16, changeCrimeW16, al2W16, al5W16),
                   \(x) weighted.mean(x, wt_new_W16, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave16 == 1) %>%
              drop_na(wt_new_W16, ageW16) %>%
              group_by(ageW16) %>%
              summarise(across(c(crime_mii_catW16, health_mii_catW16, edu_mii_catW16, wardef_mii_catW16, changeCrimeW16, al2W16, al5W16),
                               \(x) weighted.mean(x, wt_new_W16, na.rm=T))) %>%
              rename(Demographic = ageW16)) %>%
  bind_rows(subset %>%
              filter(wave16 == 1) %>%
              drop_na(wt_new_W16, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW16, health_mii_catW16, edu_mii_catW16, wardef_mii_catW16, changeCrimeW16, al2W16, al5W16),
                               \(x) weighted.mean(x, wt_new_W16, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave16 == 1) %>%
              drop_na(wt_new_W16, p_ethnicityW16) %>%
              group_by(p_ethnicityW16) %>%
              summarise(across(c(crime_mii_catW16, health_mii_catW16, edu_mii_catW16, wardef_mii_catW16, changeCrimeW16, al2W16, al5W16),
                               \(x) weighted.mean(x, wt_new_W16, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW16)) %>%
  mutate(Wave = 16) %>%
  rename(mii_crime = crime_mii_catW16, mii_health = health_mii_catW16, mii_edu = edu_mii_catW16, mii_defence = wardef_mii_catW16,
         increasecrime=changeCrimeW16, deathpen=al2W16, stiffsent=al5W16) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave17 == 1) %>%
  drop_na(wt_new_W17) %>%
  summarise(across(c(crime_mii_catW17, health_mii_catW17, edu_mii_catW17, wardef_mii_catW17, changeCrimeW17, al2W17_W19, al5W17_W19, selfPriorities_crimeW17),
                   \(x) weighted.mean(x, wt_new_W17, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave17 == 1) %>%
              drop_na(wt_new_W17, ageW17) %>%
              group_by(ageW17) %>%
              summarise(across(c(crime_mii_catW17, health_mii_catW17, edu_mii_catW17, wardef_mii_catW17, changeCrimeW17, al2W17_W19, al5W17_W19, selfPriorities_crimeW17),
                               \(x) weighted.mean(x, wt_new_W17, na.rm=T))) %>%
              rename(Demographic = ageW17)) %>%
  bind_rows(subset %>%
              filter(wave17 == 1) %>%
              drop_na(wt_new_W17, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW17, health_mii_catW17, edu_mii_catW17, wardef_mii_catW17, changeCrimeW17, al2W17_W19, al5W17_W19, selfPriorities_crimeW17),
                               \(x) weighted.mean(x, wt_new_W17, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave17 == 1) %>%
              drop_na(wt_new_W17, p_ethnicityW17) %>%
              group_by(p_ethnicityW17) %>%
              summarise(across(c(crime_mii_catW17, health_mii_catW17, edu_mii_catW17, wardef_mii_catW17, changeCrimeW17, al2W17_W19, al5W17_W19, selfPriorities_crimeW17),
                               \(x) weighted.mean(x, wt_new_W17, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW17)) %>%
  mutate(Wave = 17) %>%
  rename(mii_crime = crime_mii_catW17, mii_health = health_mii_catW17, mii_edu = edu_mii_catW17, mii_defence = wardef_mii_catW17,
         increasecrime=changeCrimeW17, deathpen=al2W17_W19, stiffsent=al5W17_W19, crimepriority=selfPriorities_crimeW17) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave18 == 1) %>%
  drop_na(wt_new_W18) %>%
  summarise(across(c(crime_mii_catW18, health_mii_catW18, edu_mii_catW18, wardef_mii_catW18),
                   \(x) weighted.mean(x, wt_new_W18, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave18 == 1) %>%
              drop_na(wt_new_W18, ageW18) %>%
              group_by(ageW18) %>%
              summarise(across(c(crime_mii_catW18, health_mii_catW18, edu_mii_catW18, wardef_mii_catW18),
                               \(x) weighted.mean(x, wt_new_W18, na.rm=T))) %>%
              rename(Demographic = ageW18)) %>%
  bind_rows(subset %>%
              filter(wave18 == 1) %>%
              drop_na(wt_new_W18, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW18, health_mii_catW18, edu_mii_catW18, wardef_mii_catW18),
                               \(x) weighted.mean(x, wt_new_W18, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave18 == 1) %>%
              drop_na(wt_new_W18, p_ethnicityW18) %>%
              group_by(p_ethnicityW18) %>%
              summarise(across(c(crime_mii_catW18, health_mii_catW18, edu_mii_catW18, wardef_mii_catW18),
                               \(x) weighted.mean(x, wt_new_W18, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW18)) %>%
  mutate(Wave = 18) %>%
  rename(mii_crime = crime_mii_catW18, mii_health = health_mii_catW18, mii_edu = edu_mii_catW18, mii_defence = wardef_mii_catW18) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave19 == 1) %>%
  drop_na(wt_new_W19_result) %>%
  summarise(across(c(crime_mii_catW19, health_mii_catW19, edu_mii_catW19, wardef_mii_catW19),
                   \(x) weighted.mean(x, wt_new_W19_result, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave19 == 1) %>%
              drop_na(wt_new_W19_result, ageW19) %>%
              group_by(ageW19) %>%
              summarise(across(c(crime_mii_catW19, health_mii_catW19, edu_mii_catW19, wardef_mii_catW19),
                               \(x) weighted.mean(x, wt_new_W19_result, na.rm=T))) %>%
              rename(Demographic = ageW19)) %>%
  bind_rows(subset %>%
              filter(wave19 == 1) %>%
              drop_na(wt_new_W19_result, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW19, health_mii_catW19, edu_mii_catW19, wardef_mii_catW19),
                               \(x) weighted.mean(x, wt_new_W19_result, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave19 == 1) %>%
              drop_na(wt_new_W19_result, p_ethnicityW19) %>%
              group_by(p_ethnicityW19) %>%
              summarise(across(c(crime_mii_catW19, health_mii_catW19, edu_mii_catW19, wardef_mii_catW19),
                               \(x) weighted.mean(x, wt_new_W19_result, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW19)) %>%
  mutate(Wave = 19) %>%
  rename(mii_crime = crime_mii_catW19, mii_health = health_mii_catW19, mii_edu = edu_mii_catW19, mii_defence = wardef_mii_catW19) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave20 == 1) %>%
  drop_na(wt_new_W20) %>%
  summarise(across(c(crime_mii_catW20, health_mii_catW20, edu_mii_catW20, wardef_mii_catW20, al2W20, al5W20, selfPriorities_crimeW20),
                   \(x) weighted.mean(x, wt_new_W20, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave20 == 1) %>%
              drop_na(wt_new_W20, ageW20) %>%
              group_by(ageW20) %>%
              summarise(across(c(crime_mii_catW20, health_mii_catW20, edu_mii_catW20, wardef_mii_catW20, al2W20, al5W20, selfPriorities_crimeW20),
                               \(x) weighted.mean(x, wt_new_W20, na.rm=T))) %>%
              rename(Demographic = ageW20)) %>%
  bind_rows(subset %>%
              filter(wave20 == 1) %>%
              drop_na(wt_new_W20, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW20, health_mii_catW20, edu_mii_catW20, wardef_mii_catW20, al2W20, al5W20, selfPriorities_crimeW20),
                               \(x) weighted.mean(x, wt_new_W20, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave20 == 1) %>%
              drop_na(wt_new_W20, p_ethnicityW20) %>%
              group_by(p_ethnicityW20) %>%
              summarise(across(c(crime_mii_catW20, health_mii_catW20, edu_mii_catW20, wardef_mii_catW20, al2W20, al5W20, selfPriorities_crimeW20),
                               \(x) weighted.mean(x, wt_new_W20, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW20)) %>%
  mutate(Wave = 20) %>%
  rename(mii_crime = crime_mii_catW20, mii_health = health_mii_catW20, mii_edu = edu_mii_catW20, mii_defence = wardef_mii_catW20,
         deathpen = al2W20, stiffsent = al5W20, crimepriority = selfPriorities_crimeW20) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave21 == 1) %>%
  drop_na(wt_new_W21) %>%
  summarise(across(c(crime_mii_catW21, health_mii_catW21, edu_mii_catW21, wardef_mii_catW21, al2W21, al5W21),
                   \(x) weighted.mean(x, wt_new_W21, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave21 == 1) %>%
              drop_na(wt_new_W21, ageW21) %>%
              group_by(ageW21) %>%
              summarise(across(c(crime_mii_catW21, health_mii_catW21, edu_mii_catW21, wardef_mii_catW21, al2W21, al5W21),
                               \(x) weighted.mean(x, wt_new_W21, na.rm=T))) %>%
              rename(Demographic = ageW21)) %>%
  bind_rows(subset %>%
              filter(wave21 == 1) %>%
              drop_na(wt_new_W21, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW21, health_mii_catW21, edu_mii_catW21, wardef_mii_catW21, al2W21, al5W21),
                               \(x) weighted.mean(x, wt_new_W21, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave21 == 1) %>%
              drop_na(wt_new_W21, p_ethnicityW21) %>%
              group_by(p_ethnicityW21) %>%
              summarise(across(c(crime_mii_catW21, health_mii_catW21, edu_mii_catW21, wardef_mii_catW21, al2W21, al5W21),
                               \(x) weighted.mean(x, wt_new_W21, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW21)) %>%
  mutate(Wave = 21) %>%
  rename(mii_crime = crime_mii_catW21, mii_health = health_mii_catW21, mii_edu = edu_mii_catW21, mii_defence = wardef_mii_catW21,
         deathpen = al2W21, stiffsent = al5W21) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave22 == 1) %>%
  drop_na(wt_new_W22) %>%
  summarise(across(c(crime_mii_catW22, health_mii_catW22, edu_mii_catW22, wardef_mii_catW22, al2W22, al5W22),
                   \(x) weighted.mean(x, wt_new_W22, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave22 == 1) %>%
              drop_na(wt_new_W22, ageW22) %>%
              group_by(ageW22) %>%
              summarise(across(c(crime_mii_catW22, health_mii_catW22, edu_mii_catW22, wardef_mii_catW22, al2W22, al5W22),
                               \(x) weighted.mean(x, wt_new_W22, na.rm=T))) %>%
              rename(Demographic = ageW22)) %>%
  bind_rows(subset %>%
              filter(wave22 == 1) %>%
              drop_na(wt_new_W22, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW22, health_mii_catW22, edu_mii_catW22, wardef_mii_catW22, al2W22, al5W22),
                               \(x) weighted.mean(x, wt_new_W22, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave22 == 1) %>%
              drop_na(wt_new_W22, p_ethnicityW22) %>%
              group_by(p_ethnicityW22) %>%
              summarise(across(c(crime_mii_catW22, health_mii_catW22, edu_mii_catW22, wardef_mii_catW22, al2W22, al5W22),
                               \(x) weighted.mean(x, wt_new_W22, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW22)) %>%
  mutate(Wave = 22) %>%
  rename(mii_crime = crime_mii_catW22, mii_health = health_mii_catW22, mii_edu = edu_mii_catW22, mii_defence = wardef_mii_catW22,
         deathpen = al2W22, stiffsent = al5W22) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave23 == 1) %>%
  drop_na(wt_new_W23) %>%
  summarise(across(c(crime_mii_catW23, health_mii_catW23, edu_mii_catW23, wardef_mii_catW23, al2W23, al5W23),
                   \(x) weighted.mean(x, wt_new_W23, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave23 == 1) %>%
              drop_na(wt_new_W23, ageW23) %>%
              group_by(ageW23) %>%
              summarise(across(c(crime_mii_catW23, health_mii_catW23, edu_mii_catW23, wardef_mii_catW23, al2W23, al5W23),
                               \(x) weighted.mean(x, wt_new_W23, na.rm=T))) %>%
              rename(Demographic = ageW23)) %>%
  bind_rows(subset %>%
              filter(wave23 == 1) %>%
              drop_na(wt_new_W23, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW23, health_mii_catW23, edu_mii_catW23, wardef_mii_catW23, al2W23, al5W23),
                               \(x) weighted.mean(x, wt_new_W23, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave23 == 1) %>%
              drop_na(wt_new_W23, p_ethnicityW23) %>%
              group_by(p_ethnicityW23) %>%
              summarise(across(c(crime_mii_catW23, health_mii_catW23, edu_mii_catW23, wardef_mii_catW23, al2W23, al5W23),
                               \(x) weighted.mean(x, wt_new_W23, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW23)) %>%
  mutate(Wave = 23) %>%
  rename(mii_crime = crime_mii_catW23, mii_health = health_mii_catW23, mii_edu = edu_mii_catW23, mii_defence = wardef_mii_catW23,
         deathpen = al2W23, stiffsent = al5W23) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave24 == 1) %>%
  drop_na(wt_new_W24) %>%
  summarise(across(c(crime_mii_catW24, health_mii_catW24, edu_mii_catW24, wardef_mii_catW24, al2W24, al5W24),
                   \(x) weighted.mean(x, wt_new_W24, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave24 == 1) %>%
              drop_na(wt_new_W24, ageW24) %>%
              group_by(ageW24) %>%
              summarise(across(c(crime_mii_catW24, health_mii_catW24, edu_mii_catW24, wardef_mii_catW24, al2W24, al5W24),
                               \(x) weighted.mean(x, wt_new_W24, na.rm=T))) %>%
              rename(Demographic = ageW24)) %>%
  bind_rows(subset %>%
              filter(wave24 == 1) %>%
              drop_na(wt_new_W24, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW24, health_mii_catW24, edu_mii_catW24, wardef_mii_catW24, al2W24, al5W24),
                               \(x) weighted.mean(x, wt_new_W24, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave24 == 1) %>%
              drop_na(wt_new_W24, p_ethnicityW24) %>%
              group_by(p_ethnicityW24) %>%
              summarise(across(c(crime_mii_catW24, health_mii_catW24, edu_mii_catW24, wardef_mii_catW24, al2W24, al5W24),
                               \(x) weighted.mean(x, wt_new_W24, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW24)) %>%
  mutate(Wave = 24) %>%
  rename(mii_crime = crime_mii_catW24, mii_health = health_mii_catW24, mii_edu = edu_mii_catW24, mii_defence = wardef_mii_catW24,
         deathpen = al2W24, stiffsent = al5W24) %>%
  bind_rows(OutPut, .) -> OutPut


subset %>%
  filter(wave25 == 1) %>%
  drop_na(wt_new_W25) %>%
  summarise(across(c(crime_mii_catW25, health_mii_catW25, edu_mii_catW25, wardef_mii_catW25, changeCrimeW25, al2W25, al5W25),
                   \(x) weighted.mean(x, wt_new_W25, na.rm=T))) %>%
  mutate(Demographic = "All adults") %>%
  bind_rows(subset %>%
              filter(wave25 == 1) %>%
              drop_na(wt_new_W25, ageW25) %>%
              group_by(ageW25) %>%
              summarise(across(c(crime_mii_catW25, health_mii_catW25, edu_mii_catW25, wardef_mii_catW25, changeCrimeW25, al2W25, al5W25),
                               \(x) weighted.mean(x, wt_new_W25, na.rm=T))) %>%
              rename(Demographic = ageW25)) %>%
  bind_rows(subset %>%
              filter(wave25 == 1) %>%
              drop_na(wt_new_W25, gender) %>%
              group_by(gender) %>%
              summarise(across(c(crime_mii_catW25, health_mii_catW25, edu_mii_catW25, wardef_mii_catW25, changeCrimeW25, al2W25, al5W25),
                               \(x) weighted.mean(x, wt_new_W25, na.rm=T))) %>%
              rename(Demographic = gender)) %>%
  bind_rows(subset %>%
              filter(wave25 == 1) %>%
              drop_na(wt_new_W25, p_ethnicityW25) %>%
              group_by(p_ethnicityW25) %>%
              summarise(across(c(crime_mii_catW25, health_mii_catW25, edu_mii_catW25, wardef_mii_catW25, changeCrimeW25, al2W25, al5W25),
                               \(x) weighted.mean(x, wt_new_W25, na.rm=T))) %>%
              rename(Demographic = p_ethnicityW25)) %>%
  mutate(Wave = 25) %>%
  rename(mii_crime = crime_mii_catW25, mii_health = health_mii_catW25, mii_edu = edu_mii_catW25, mii_defence = wardef_mii_catW25,
         increasecrime = changeCrimeW25, deathpen = al2W25, stiffsent = al5W25) %>%
  bind_rows(OutPut, .) -> OutPut


ncases <- tibble(Wave = numeric(), Demographic = character(), n = numeric())
for (i in c(1:25)){
  var1 <- paste0('wave',i)
  var2 <- paste0('ageW',i)
  var3 <- paste0('p_ethnicityW',i)
  subset %>% 
    filter(eval(get(var1)) == 1) %>%
    summarise(n = n()) %>%
    mutate(Demographic = "All adults") %>%
    bind_rows(.,subset %>% 
                filter(eval(get(var1)) == 1) %>%
                group_by(eval(get(var2))) %>%
                summarise(n = n()) %>%
                rename(Demographic = `eval(get(var2))`)) %>%
    bind_rows(.,subset %>% 
                filter(eval(get(var1)) == 1) %>%
                group_by(gender) %>%
                summarise(n = n()) %>%
                rename(Demographic = gender)) %>%
    bind_rows(.,subset %>% 
                filter(eval(get(var1)) == 1) %>%
                group_by(eval(get(var3))) %>%
                summarise(n = n()) %>%
                rename(Demographic = `eval(get(var3))`)) %>%
    select(Demographic, n) %>%
    drop_na(Demographic) %>%
    mutate(Wave = i) %>%
    bind_rows(ncases, .) -> ncases
}


OutPut %>%
  rename(increasecrimeBSIP = increasecrime, deathpenBSIP = deathpen, stiffsentBESIP = stiffsent) %>%
  pivot_longer(-c(Wave, Demographic)) %>%
  rename(Varname = name, Index = value) %>%
  mutate(Poll = "BES Internet Panel",
         Date = case_match(Wave,
                           1 ~ "2014-02-01",
                           2 ~ "2014-05-01",
                           3 ~ "2014-10-01",
                           4 ~ "2015-03-01",
                           5 ~ "2015-04-01",
                           6 ~ "2015-05-01",
                           7 ~ "2016-04-01",
                           8 ~ "2016-06-01",
                           9 ~ "2016-07-01",
                           10 ~ "2016-12-01",
                           11 ~ "2017-04-01",
                           12 ~ "2017-05-01",
                           13 ~ "2017-06-01",
                           14 ~ "2018-05-01",
                           15 ~ "2019-03-01",
                           16 ~ "2019-06-01",
                           17 ~ "2019-11-01",
                           18 ~ "2019-11-20",
                           19 ~ "2019-12-01",
                           20 ~ "2020-06-01",
                           21 ~ "2021-05-01",
                           22 ~ "2021-12-01",
                           23 ~ "2022-05-01",
                           24 ~ "2022-12-01",
                           25 ~ "2023-05-01")) -> OutPut

OutPut %>%
  merge(., ncases, by = c('Wave', 'Demographic')) %>%
  select(-Wave) %>%
  select(Date, Varname, Index, Demographic, n, Poll) %>%
  drop_na(Index) %>%
  write_csv(., "/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Finished/BESIP_summaries.csv")

